/*
EXEC sp_load_all_work_orders_running_or_closed_in_date_range '0', 0, 0, '0', '0', 0, 'byDueDate', '03/30/2006', '0','0', '0'
EXEC sp_load_all_work_orders_running_or_closed_in_date_range '0', 0, 0, '0', '0','0', '0', '0', '0','0', '0'

select DATEDIFF(D,'2006-03-28 21:24:44.420','2006-03-30 21:24:44.420')

*/
Alter PROC sp_load_all_work_orders_running_or_closed_in_date_range
(
	@PS_TYPE_NAME 	VARCHAR(50),
	@BASE_PRODUCT_ID	INT,
	@PROCESSSTEP_ID 	INT,
	@CARD_TYPE		VARCHAR(50),
	@PACKAGE_TYPE	VARCHAR(50),
	@MEMORY_SIZE		VARCHAR(50),
	@DATE_TYPE		VARCHAR(20),
	@FROM_DATE		VARCHAR(20),
	@TO_DATE		VARCHAR(20),
	@STATUS		VARCHAR(20),
	@DEFAULT_PROMPT 	VARCHAR(20)
)

AS
BEGIN
	
	DECLARE @SQL_STRING NVARCHAR(4000)
	DECLARE @SQLSTRING   NVARCHAR(4000)
	DECLARE @SQLSTRING1 NVARCHAR(4000)
	SET @SQLSTRING = ''

	SET @SQL_STRING = 
	  N'SELECT CASE 
			WHEN p_type is Null THEN 1
			WHEN p_type = ''BOARD'' THEN 2
			WHEN p_type =''KIT'' THEN 3
		       END AS PRODUCT_TYPE_ID, 
		(SELECT PART_NUMBER FROM PRODUCT WHERE PRODUCT_ID = CASE 
			WHEN P.P_PARENT_PRODUCT_ID = -1 THEN P.PRODUCT_ID 
			WHEN P.P_PARENT_PRODUCT_ID <> -1 THEN P.P_PARENT_PRODUCT_ID	
		 END 
		) AS BASE_PRODUCT_NAME,
		CASE 
			WHEN PS_TYPE_NAME = ''FAB'' THEN 1
			WHEN PS_TYPE_NAME = ''Wafer Store'' THEN 2
			WHEN PS_TYPE_NAME = ''SORT'' THEN 3
			WHEN PS_TYPE_NAME = ''Die Bank'' THEN 4
			WHEN PS_TYPE_NAME = ''Assembly'' THEN 5
			WHEN PS_TYPE_NAME = ''TEST'' THEN 6
			WHEN PS_TYPE_NAME = ''Class Store'' THEN 7
			WHEN PS_TYPE_NAME = ''Mark & Pack'' or PS_TYPE_NAME = ''Mark &amp; Pack'' THEN 8
			WHEN PS_TYPE_NAME = ''Finished Goods'' THEN 9
			ELSE 0
		END AS PS_TYPE_NAME_ID,
		PS_TYPE_NAME,
		P.PROCESSSTEP_ID,
		QT.ITEM_NUMBER, 
		QT.ITEM_DESC, 
		LT.LOT_ID,
		QT.WORK_ORDER_NO, WRLT.QTY_OUT, WRLT.QTY_REJECTED, WRLT.QTY_IN, WRLT.ISSUE_DATE , QT.REL_DATE AS QAD_REL_DATE, QT.DUE_DATE AS QAD_DUE_DATE, 
		( CASE
			WHEN LOT_CURRENT_STATUS = ''CLOSE'' THEN (SELECT TOP 1 ALH.AL_EXPECTED_DATE_OUT  FROM ASSEMBLY_LOT_HISTORY ALH WHERE LOT_ID = LT.LOT_ID ORDER BY TIMESTAMP DESC ) 
			ELSE
			(SELECT AL.AL_EXPECTED_DATE_OUT  FROM ASSEMBLY_LOT AL WHERE LOT_ID = LT.LOT_ID)  END
		) AS SOD,
		DATEDIFF(D, QT.DUE_DATE , CASE
						WHEN LOT_CURRENT_STATUS = ''CLOSE'' THEN (SELECT TOP 1 ISNULL( AL_REVISED_DATE_OUT, AL_EXPECTED_DATE_OUT )  FROM ASSEMBLY_LOT_HISTORY  WHERE LOT_ID = LT.LOT_ID ORDER BY TIMESTAMP DESC ) 
						ELSE
						(SELECT ISNULL( AL.AL_REVISED_DATE_OUT, AL.AL_EXPECTED_DATE_OUT )  FROM ASSEMBLY_LOT AL WHERE LOT_ID = LT.LOT_ID) END 
		) AS [QAD SOD LATE DAYS],
		( CASE
			WHEN LOT_CURRENT_STATUS = ''CLOSE'' THEN (SELECT TOP 1 ALH.AL_REVISED_DATE_OUT  FROM ASSEMBLY_LOT_HISTORY ALH WHERE LOT_ID = LT.LOT_ID ORDER BY TIMESTAMP DESC ) 
			ELSE
			(SELECT AL.AL_REVISED_DATE_OUT  FROM ASSEMBLY_LOT AL WHERE LOT_ID = LT.LOT_ID)  END 
		) AS REVISE_SOD,
		(  CASE
			WHEN LOT_CURRENT_STATUS = ''CLOSE'' THEN (SELECT TOP 1 ALH.AL_LOAD_DATE  FROM ASSEMBLY_LOT_HISTORY ALH WHERE LOT_ID = LT.LOT_ID ORDER BY TIMESTAMP DESC ) 
			ELSE
			(SELECT AL.AL_LOAD_DATE  FROM ASSEMBLY_LOT AL WHERE LOT_ID = LT.LOT_ID) END
		) AS LOAD_DATE,
		( CASE
			WHEN LOT_CURRENT_STATUS = ''CLOSE'' THEN (SELECT TOP 1 ALH.AL_QTY_IN  FROM ASSEMBLY_LOT_HISTORY ALH WHERE LOT_ID = LT.LOT_ID ORDER BY TIMESTAMP DESC ) 
			ELSE
			(SELECT AL.AL_QTY_IN  FROM ASSEMBLY_LOT AL WHERE LOT_ID = LT.LOT_ID ) END
		) AS ASSM_QTY,
		( SELECT ASL_QTY_IN  FROM ASSEMBLY_STAGE_LOT ASL, ASSEMBLY_STAGE_TABLE AST 
		  WHERE ASL.STAGE_ID = AST.STAGE_ID AND AST_STAGE_NAME = ''2nd Optical Inspection'' AND LOT_ID = LT.LOT_ID
		) AS [2/O],
		( SELECT ASL_QTY_IN  FROM ASSEMBLY_STAGE_LOT ASL, ASSEMBLY_STAGE_TABLE AST 
		  WHERE ASL.STAGE_ID = AST.STAGE_ID AND AST_STAGE_NAME = ''Die Attach'' AND LOT_ID = LT.LOT_ID
		) AS [DA],
		( SELECT ASL_QTY_IN  FROM ASSEMBLY_STAGE_LOT ASL, ASSEMBLY_STAGE_TABLE AST 
		  WHERE ASL.STAGE_ID = AST.STAGE_ID AND AST_STAGE_NAME = ''Wire Bond'' AND LOT_ID = LT.LOT_ID
		) AS [WB],
		( SELECT ASL_QTY_IN  FROM ASSEMBLY_STAGE_LOT ASL, ASSEMBLY_STAGE_TABLE AST 
		  WHERE ASL.STAGE_ID = AST.STAGE_ID AND AST_STAGE_NAME = ''Molding'' AND LOT_ID = LT.LOT_ID
		) AS [MOLD],
		( SELECT ASL_QTY_IN  FROM ASSEMBLY_STAGE_LOT ASL, ASSEMBLY_STAGE_TABLE AST 
		  WHERE ASL.STAGE_ID = AST.STAGE_ID AND AST_STAGE_NAME = ''Plating'' AND LOT_ID = LT.LOT_ID
		) AS [PLAT],
		( SELECT ASL_QTY_IN  FROM ASSEMBLY_STAGE_LOT ASL, ASSEMBLY_STAGE_TABLE AST 
		  WHERE ASL.STAGE_ID = AST.STAGE_ID AND AST_STAGE_NAME = ''Forming'' AND LOT_ID = LT.LOT_ID
		) AS [FORM],
		( SELECT ASL_QTY_IN  FROM ASSEMBLY_STAGE_LOT ASL, ASSEMBLY_STAGE_TABLE AST 
		  WHERE ASL.STAGE_ID = AST.STAGE_ID AND AST_STAGE_NAME = ''Packing'' AND LOT_ID = LT.LOT_ID
		) AS [PACK], QT.WO_STATUS, '

SET @SQLSTRING1 = N'( SELECT top 1 AL_QTY_OUT  FROM ASSEMBLY_LOT_HISTORY ALH WHERE LOT_ID = LT.LOT_ID AND LOT_CURRENT_STATUS = ''CLOSE'' order by ALH.timestamp desc ) AS VENDOR_QTY_OUT,
		--FOR SCHEDULE QAD RELEASE DATE VS ACTUAL RELEASE DATE ( RELEASE DATE - LOAD DATE)
		DATEDIFF(D, WRLT.RELEASE_DATE , 
		(  CASE
			WHEN LOT_CURRENT_STATUS = ''CLOSE'' THEN (SELECT TOP 1 ALH.AL_LOAD_DATE  FROM ASSEMBLY_LOT_HISTORY ALH WHERE LOT_ID = LT.LOT_ID ORDER BY TIMESTAMP DESC ) 
			ELSE
			(SELECT AL.AL_LOAD_DATE  FROM ASSEMBLY_LOT AL WHERE LOT_ID = LT.LOT_ID) END
		)  ) AS [WO NOT RELEASED IN TIME],
		--FOR ACTAUL COMPLETED DATE VS QAD REQUIRED DUE DATE (DUE DATE - DATE OUT)
		DATEDIFF(D, QT.DUE_DATE, (SELECT top 1 ISNULL( AHL.AL_DATE_OUT, AHL.TIMESTAMP )  FROM ASSEMBLY_LOT_HISTORY AHL WHERE LOT_ID = LT.LOT_ID order by timestamp desc)  ) AS [QAD LATE DAYS],
		--FOR ACTUAL COMPLETE DATE VS VENDOR COMMIT (DATE OUT - SOD)
		DATEDIFF(D,  (SELECT TOP 1 ISNULL ( AL_REVISED_DATE_OUT, AL_EXPECTED_DATE_OUT )  FROM ASSEMBLY_LOT_HISTORY ALH WHERE LOT_ID = LT.LOT_ID ORDER BY TIMESTAMP DESC ),
			       (SELECT top 1 ISNULL( AHL.AL_DATE_OUT, AHL.TIMESTAMP )  FROM ASSEMBLY_LOT_HISTORY AHL WHERE LOT_ID = LT.LOT_ID order by timestamp desc)  ) AS [VENDOR LATE DAYS],
		(SELECT top 1 ISNULL( AHL.AL_DATE_OUT, AHL.TIMESTAMP )  FROM ASSEMBLY_LOT_HISTORY AHL WHERE LOT_ID = LT.LOT_ID order by timestamp desc) as [DATE OUT]
		
	
	FROM LOT_TABLE LT, QAD_TABLE QT, PRODUCT P, PROCESS_STEP PS, WORK_ORDER_LOT_TABLE WRLT
	WHERE 
		QT.WORK_ORDER_NO = LT.LOT_NUMBER AND 
		QT.ITEM_NUMBER = P.PART_NUMBER AND 
		LT.PROCESSSTEP_ID = P.PROCESSSTEP_ID  AND 
		P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID AND 
		LT.LOT_ID = WRLT.LOT_ID '
		
	--TO LOAD RUNNING OR CLOSED LOTS BASED ON THE RADIO BUTTON SELECTED.
	IF ( @STATUS = 'CLOSE' )
	BEGIN
		SET @SQLSTRING1 = @SQLSTRING1 + ' AND WO_STATUS = ''CLOSE'' '
	END
	ELSE
	BEGIN
		SET @SQLSTRING1 = @SQLSTRING1 + ' AND WO_STATUS = ''RUNNING'' and
		(  	( SELECT ASL_QTY_IN  FROM ASSEMBLY_STAGE_LOT ASL, ASSEMBLY_STAGE_TABLE AST 
			   WHERE ASL.STAGE_ID = AST.STAGE_ID AND AST_STAGE_NAME = ''2nd Optical Inspection'' AND LOT_ID = LT.LOT_ID
			   ) > 0 or
			( SELECT ASL_QTY_IN  FROM ASSEMBLY_STAGE_LOT ASL, ASSEMBLY_STAGE_TABLE AST 
			  WHERE ASL.STAGE_ID = AST.STAGE_ID AND AST_STAGE_NAME = ''Die Attach'' AND LOT_ID = LT.LOT_ID
			) > 0 or
			( SELECT ASL_QTY_IN  FROM ASSEMBLY_STAGE_LOT ASL, ASSEMBLY_STAGE_TABLE AST 
			  WHERE ASL.STAGE_ID = AST.STAGE_ID AND AST_STAGE_NAME = ''Wire Bond'' AND LOT_ID = LT.LOT_ID
			) > 0 or
			( SELECT ASL_QTY_IN  FROM ASSEMBLY_STAGE_LOT ASL, ASSEMBLY_STAGE_TABLE AST 
			  WHERE ASL.STAGE_ID = AST.STAGE_ID AND AST_STAGE_NAME = ''Molding'' AND LOT_ID = LT.LOT_ID
			) > 0 or
			( SELECT ASL_QTY_IN  FROM ASSEMBLY_STAGE_LOT ASL, ASSEMBLY_STAGE_TABLE AST 
			  WHERE ASL.STAGE_ID = AST.STAGE_ID AND AST_STAGE_NAME = ''Plating'' AND LOT_ID = LT.LOT_ID
			) > 0 or
			( SELECT ASL_QTY_IN  FROM ASSEMBLY_STAGE_LOT ASL, ASSEMBLY_STAGE_TABLE AST 
			  WHERE ASL.STAGE_ID = AST.STAGE_ID AND AST_STAGE_NAME = ''Forming'' AND LOT_ID = LT.LOT_ID
			) > 0 or
			( SELECT ASL_QTY_IN  FROM ASSEMBLY_STAGE_LOT ASL, ASSEMBLY_STAGE_TABLE AST 
			  WHERE ASL.STAGE_ID = AST.STAGE_ID AND AST_STAGE_NAME = ''Packing'' AND LOT_ID = LT.LOT_ID
			) > 0 
		)'

	END

	
	--Search condition for Base Product when @CARD_TYPE, @PACKAGE_TYPE, @MEMORY_SIZE not selected
	IF ( @BASE_PRODUCT_ID IS NOT NULL  AND @BASE_PRODUCT_ID <> 0 ) and (@CARD_TYPE = '0'and @PACKAGE_TYPE = '0' and @MEMORY_SIZE = '0' )
	BEGIN
		SET @SQLSTRING = @SQLSTRING +' and LT.processstep_id in ( SELECT PROCESSSTEP_ID FROM PRODUCT WHERE P_PARENT_PRODUCT_ID = '+convert(varchar,@BASE_PRODUCT_ID) +')'
	END

	--Search condition for Part Number when @CARD_TYPE, @PACKAGE_TYPE, @MEMORY_SIZE not selected
	IF ( @PROCESSSTEP_ID IS NOT NULL AND @PROCESSSTEP_ID <> 0 ) and (@CARD_TYPE = '0' and @PACKAGE_TYPE = '0' and @MEMORY_SIZE = '0' )
	BEGIN
		SET @SQLSTRING = @SQLSTRING +' and LT.processstep_id = '+convert(varchar,@PROCESSSTEP_ID)
	END

	--Search condition for @CARD_TYPE
	IF ( @CARD_TYPE IS NOT NULL AND ltrim(rtrim(@CARD_TYPE)) <> '' AND @CARD_TYPE <> '0' )
	BEGIN
		IF ( @BASE_PRODUCT_ID IS NOT NULL  AND @BASE_PRODUCT_ID <> 0 )
		BEGIN
			SET @SQLSTRING = @SQLSTRING +' and LT.processstep_id in ( SELECT P.PROCESSSTEP_ID FROM PRODUCT P WHERE P.P_PARENT_PRODUCT_ID = '+convert(varchar,@BASE_PRODUCT_ID)+
			' AND (SELECT distinct PARAMETER_VALUE FROM PROCESS_PARAMETER WHERE PARAMETER_KEY = ''CARD TYPE'' and processstep_id = p.processstep_id ) = ''' +  @CARD_TYPE + ''''+')'
		END
		
		IF ( @PROCESSSTEP_ID IS NOT NULL AND @PROCESSSTEP_ID <> 0 )
		BEGIN
			SET @SQLSTRING = @SQLSTRING +' and LT.processstep_id in (SELECT processstep_id FROM PROCESS_PARAMETER WHERE PARAMETER_KEY = ''CARD TYPE'' AND PROCESSSTEP_ID ='+convert(varchar,@PROCESSSTEP_ID)+' and PARAMETER_VALUE = ''' +  @CARD_TYPE + ''''+')'
		END

		IF ( @BASE_PRODUCT_ID = 0 AND @PROCESSSTEP_ID = 0 )
		BEGIN
			SET @SQLSTRING = @SQLSTRING +' and LT.processstep_id in ( SELECT P.PROCESSSTEP_ID FROM PRODUCT P WHERE (SELECT distinct PARAMETER_VALUE FROM PROCESS_PARAMETER WHERE PARAMETER_KEY = ''CARD TYPE'' and processstep_id = LT.processstep_id ) = ''' +  @CARD_TYPE + ''''+')'
		END
	END

	--Search condition for @PACKAGE_TYPE
	IF ( @PACKAGE_TYPE IS NOT NULL AND ltrim(rtrim(@PACKAGE_TYPE)) <> '' AND @PACKAGE_TYPE <> '0' ) 
	BEGIN
		IF ( @BASE_PRODUCT_ID IS NOT NULL  AND @BASE_PRODUCT_ID <> 0 )
		BEGIN
			SET @SQLSTRING = @SQLSTRING +' and LT.processstep_id in (SELECT P.PROCESSSTEP_ID FROM PRODUCT P WHERE P.P_PARENT_PRODUCT_ID = '+convert(varchar,@BASE_PRODUCT_ID)+
			' AND (SELECT distinct PARAMETER_VALUE FROM PROCESS_PARAMETER WHERE PARAMETER_KEY = ''PACKAGE ID'' and processstep_id = p.processstep_id ) =  ''' +  @PACKAGE_TYPE + ''''+')'
		END

		IF ( @PROCESSSTEP_ID IS NOT NULL  AND @PROCESSSTEP_ID <> 0 )
		BEGIN
			SET @SQLSTRING = @SQLSTRING +' and LT.processstep_id in (SELECT processstep_id FROM PROCESS_PARAMETER WHERE PARAMETER_KEY = ''PACKAGE ID''AND PROCESSSTEP_ID ='+convert(varchar,@PROCESSSTEP_ID)+' and PARAMETER_VALUE = ''' +  @PACKAGE_TYPE + ''''+')'
		END
		
		IF ( @BASE_PRODUCT_ID = 0 AND @PROCESSSTEP_ID = 0 )
		BEGIN
			SET @SQLSTRING = @SQLSTRING +' and LT.processstep_id in ( SELECT P.PROCESSSTEP_ID FROM PRODUCT P WHERE (SELECT distinct PARAMETER_VALUE FROM PROCESS_PARAMETER WHERE PARAMETER_KEY = ''PACKAGE ID'' and processstep_id = LT.processstep_id ) = ''' +  @PACKAGE_TYPE + ''''+')'
		END
	END

	--Search condition for @MEMORY_SIZE
	IF ( @MEMORY_SIZE IS NOT NULL AND ltrim(rtrim(@MEMORY_SIZE)) <> '' AND @MEMORY_SIZE > '0' )  
	BEGIN
		IF ( @BASE_PRODUCT_ID IS NOT NULL AND @BASE_PRODUCT_ID <> 0 )
		BEGIN
			SET @SQLSTRING = @SQLSTRING +' and LT.processstep_id in (SELECT P.PROCESSSTEP_ID FROM PRODUCT P WHERE P.P_PARENT_PRODUCT_ID = '+convert(varchar,@BASE_PRODUCT_ID)+
			' AND (SELECT distinct PARAMETER_VALUE FROM PROCESS_PARAMETER WHERE PARAMETER_KEY = ''MEMORY SIZE'' and processstep_id = p.processstep_id ) =  ''' +  @MEMORY_SIZE + ''''+')'
		END

		IF ( @PROCESSSTEP_ID IS NOT NULL  AND @PROCESSSTEP_ID <> 0 )
		BEGIN
			SET @SQLSTRING = @SQLSTRING +' and LT.processstep_id in (SELECT processstep_id FROM PROCESS_PARAMETER WHERE PARAMETER_KEY = ''MEMORY SIZE'' AND PROCESSSTEP_ID ='+convert(varchar,@PROCESSSTEP_ID)+' and PARAMETER_VALUE   =  ''' +  @MEMORY_SIZE + ''''+')'
		END

		IF ( @BASE_PRODUCT_ID = 0 AND @PROCESSSTEP_ID = 0 )
		BEGIN
			SET @SQLSTRING = @SQLSTRING +' and LT.processstep_id in ( SELECT P.PROCESSSTEP_ID FROM PRODUCT P WHERE (SELECT distinct PARAMETER_VALUE FROM PROCESS_PARAMETER WHERE PARAMETER_KEY = ''MEMORY SIZE'' and processstep_id = LT.processstep_id )  =  ''' +  @MEMORY_SIZE + ''''+')'
		END
	END

	--For Dates
	IF @DATE_TYPE = 'byDueDate'
	BEGIN
		IF ( @FROM_DATE IS NOT NULL AND ltrim(rtrim(@FROM_DATE)) <> '' AND @FROM_DATE <> '0' ) 
		BEGIN
			set @SQLSTRING = @SQLSTRING + ' and QT.DUE_DATE >= ''' + convert(varchar, convert(datetime,@FROM_DATE) ) + ''''
		END
		IF ( @TO_DATE IS NOT NULL AND ltrim(rtrim(@TO_DATE)) <> '' AND @TO_DATE <> '0' ) 
		BEGIN
			set @SQLSTRING = @SQLSTRING + ' and QT.DUE_DATE  <= ''' + convert(varchar, convert(datetime,@TO_DATE) )  + ''''
		END
	END
	ELSE --IF @DATE_TYPE = 'byRelDate'
	BEGIN
		IF ( @FROM_DATE IS NOT NULL AND ltrim(rtrim(@FROM_DATE)) <> '' AND @FROM_DATE <> '0' ) 
		BEGIN
			set @SQLSTRING = @SQLSTRING + ' and QT.REL_DATE >= ''' + convert(varchar, convert(datetime,@FROM_DATE) ) + ''''
		END

		IF ( @TO_DATE IS NOT NULL AND ltrim(rtrim(@TO_DATE)) <> '' AND @TO_DATE <> '0' ) 
		BEGIN
			set @SQLSTRING = @SQLSTRING + ' and QT.REL_DATE <= ''' + convert(varchar, convert(datetime,@TO_DATE) )  + ''''
		END		
	END

	--PRINT @SQL_STRING
	--PRINT @SQLSTRING1
	--PRINT @SQLSTRING
	EXEC (@SQL_STRING+@SQLSTRING1+@SQLSTRING)

END